<!DOCTYPE html>
<html lang="zh-cmn-Hans" prefix="og: http://ogp.me/ns#" class="han-init">
<head>
  <meta charset="utf-8">
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
  <link rel="icon" href="http://upeng.github.io/favicon.ico">
  <title>upeng</title>
  <link rel="stylesheet" href="/css/style.css">
  <link rel="stylesheet" href="/lib/fancybox/jquery.fancybox-1.3.4.css">
  <!--在这里倒入jquery 方便处理部分页面的jquery-->
  <script src="https://cdn.staticfile.org/jquery/1.7/jquery.min.js" type="text/javascript" ></script>
</head>

<body>
	<header class="site-header navfixed-false">
  <div class="container">
      <h1><a href="/" title="upeng"><span class="octicon octicon-mark-github"></span> upeng</a></h1>
      <nav class="site-header-nav" role="navigation">
        
              
              <a href="/"  class=" site-header-nav-item hvr-underline-from-center" title="Home">Home</a>
        
              
              <a href="/categories/"  class=" site-header-nav-item hvr-underline-from-center" title="Categories">Categories</a>
        
              
              <a href="/bookmark/"  class=" site-header-nav-item hvr-underline-from-center" title="Bookmark">Bookmark</a>
        
              
              <a href="http://shareup.sinaapp.com"  class=" site-header-nav-item hvr-underline-from-center" title="Share">Share</a>
        
              
              <a href="/about/"  class=" site-header-nav-item hvr-underline-from-center" title="About">About</a>
        
      </nav>
  </div>
</header>

	
<section class="collection-head geopattern" data-pattern-id="mysql之sql优化-慢查询日志开启和查看（1）" >
    <div class="container">
        <div class="collection-title">
            <h1 class="collection-header">
                mysql之sql优化-慢查询日志开启和查看（1）
            </h1>
            
                <div class="collection-info">
                    <span class="meta-info">
                        <span class="octicon octicon-calendar"></span>
                        <time datetime="2016-04-23T19:25:05.000Z" itemprop="datePublished">2016-04-24</time>
                    </span>
                    
                        <span class="meta-info">
                            <span class="octicon octicon-file-directory"></span>
                            <a href='/categories/MySQL/' title=''>MySQL</a>
                        </span>
                    
                </div>
            
        </div>
    </div>
</section>
	<section class="container">
    <div class="columns">
        <!-- -->
        <div class="column three-fourths">
            <article class="article-content markdown-body">
                <p>如何发现有问题的SQL？使用慢查询日志【slow_query_log】对有效率问题的sql进行监控</p>
<h3 id="查看慢查询日志开启状态"><a href="#查看慢查询日志开启状态" class="headerlink" title="查看慢查询日志开启状态"></a>查看慢查询日志开启状态</h3><pre><code>show variable  s like &#39;slow_query_log&#39;
</code></pre><p><img src="https://raw.githubusercontent.com/upeng/upeng.github.io/master/image/6a0acb38-fbe0-42e6-b9ca-f05955ca73e8.png" alt=""></p>
<h3 id="记录未使用索引的查询"><a href="#记录未使用索引的查询" class="headerlink" title="记录未使用索引的查询"></a>记录未使用索引的查询</h3><p>未使用索引的查询默认是关闭的，如下</p>
<pre><code>show variables like &#39;log_queries_not_using_indexes&#39;;
</code></pre><p><img src="https://raw.githubusercontent.com/upeng/upeng.github.io/master/image/2655ccd1-69ab-4c54-b7bf-50b01b2b45e5.png" alt=""><br>需要打开未使用索引的查询，即未使用索引的查询会记录在案</p>
<pre><code>set global log_queries_not_using_indexes=on
</code></pre><a id="more"></a>
<h3 id="慢查询时间设置"><a href="#慢查询时间设置" class="headerlink" title="慢查询时间设置"></a>慢查询时间设置</h3><p> 查询时间超过多长时间的查询记录会记录到日志中； 默认为慢查询的时间10秒，即 超过10s的查询会记录到慢查询日志中<br><img src="https://raw.githubusercontent.com/upeng/upeng.github.io/master/image/d99d63ee-d0e2-4bfb-8230-984d884b1528.png" alt=""> </p>
<pre><code>set global long_query_time=0;
</code></pre><p>a litter bug i think：在本窗口查询时发现慢查询时间还是10；但是重新打开一个窗口查询结果才是0<br>这样设置的目的是：不管什么查询，都会记录到慢查询日志中</p>
<h3 id="打开慢查询日志"><a href="#打开慢查询日志" class="headerlink" title="打开慢查询日志"></a>打开慢查询日志</h3><p>设置完上述两项后，我们再打开慢查询日志</p>
<pre><code>set global slow_query_log=on;
</code></pre><p>输入两个查询语句</p>
<pre><code>select * from actor limit 10;
select * from actor where first_name like &#39;%A%&#39;;
</code></pre><h3 id="打开日志文件"><a href="#打开日志文件" class="headerlink" title="打开日志文件"></a>打开日志文件</h3><p>日志文件位置，查看变量slow_query_log_file的值</p>
<pre><code>show variables like &#39;slow_query_log_file&#39;;
</code></pre><p>当然，我们也可以手动设置慢查询日志的存放路径及文件名</p>
<pre><code>set global slow_query_log_file = ****
</code></pre><h3 id="慢查询日志所包含的内容"><a href="#慢查询日志所包含的内容" class="headerlink" title="慢查询日志所包含的内容"></a>慢查询日志所包含的内容</h3><p><img src="https://raw.githubusercontent.com/upeng/upeng.github.io/master/image/e3f793a8-8b50-464c-b52b-5ffbc78ab187.png" alt=""> </p>
<ol>
<li>执行sql的主机信息<br><code># User@Host: root[root] @ localhost [127.0.0.1]</code></li>
<li>sql的执行信息<br><code># Query_time: 0.001008  Lock_time: 0.000000 Rows_sent: 104  Rows_examined: 200</code></li>
<li>sql执行时刻<br><code>SET timestamp=1461435221;</code></li>
<li>sql内容<br><code>select * from actor where first_name like &#39;%A%&#39;;</code></li>
</ol>
<h3 id="慢查询日志的分析工具"><a href="#慢查询日志的分析工具" class="headerlink" title="慢查询日志的分析工具"></a>慢查询日志的分析工具</h3><p>mysqldumpslow输出</p>

            </article>
            
                <div class="share">
                    <!--开启分享-->
<div class="share-component" data-disabled="google,twitter,facebook" data-description="如何发现有问题的SQL？使用慢查询日志【slow_qu..."></div>

<script src="/js/share.min.js"></script>

                </div>    
            
            
                
<div class="comments">
	<div class="ds-thread" data-thread-key="mysqlperformance01" data-title="mysql之sql优化-慢查询日志开启和查看（1）" data-url="http://upeng.github.io/2016/04/24/mysqlperformance01/"></div>
	<script type="text/javascript">
	var duoshuoQuery = {short_name:"upeng"};
		(function() {
			var ds = document.createElement('script');
			ds.type = 'text/javascript';ds.async = true;
			ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
			ds.charset = 'UTF-8';
			(document.getElementsByTagName('head')[0]
			 || document.getElementsByTagName('body')[0]).appendChild(ds);
		})();
	</script>
</div>

            
        </div>
        <div class="column one-fourth">
            
                
                


<h3>Post Directory</h3>

<div id="post-directory-module">
	<section class="post-directory">
		<p><strong class="toc-title">文章目录</strong></p>
		<ol class="toc"><li class="toc-item toc-level-3"><a class="toc-link" href="#查看慢查询日志开启状态"><span class="toc-text">查看慢查询日志开启状态</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#记录未使用索引的查询"><span class="toc-text">记录未使用索引的查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#慢查询时间设置"><span class="toc-text">慢查询时间设置</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#打开慢查询日志"><span class="toc-text">打开慢查询日志</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#打开日志文件"><span class="toc-text">打开日志文件</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#慢查询日志所包含的内容"><span class="toc-text">慢查询日志所包含的内容</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#慢查询日志的分析工具"><span class="toc-text">慢查询日志的分析工具</span></a></li></ol>
	</section>
</div>
            
        </div>
    </div>
</section>

<footer class="container">
    <div class="site-footer" role="contentinfo">
        <div class="copyright left mobile-block">
                © 2016
                <span title="yupeng">yupeng</span>
                <a href="javascript:window.scrollTo(0,0)" class="right mobile-visible">TOP</a>
        </div>

        <ul class="site-footer-links right mobile-hidden">
            <li>
                <a href="javascript:window.scrollTo(0,0)" >TOP</a>
            </li>
        </ul>

        <a href="https://github.com/upeng" target="_blank" aria-label="view source code">
            <span class="mega-octicon octicon-mark-github" title="GitHub"></span>
        </a>

        <ul class="site-footer-links mobile-hidden">
            
                  
                  <li>
                    <a href="/"  title="Home">Home</a>
                  </li>
            
                  
                  <li>
                    <a href="/categories/"  title="Categories">Categories</a>
                  </li>
            
                  
                  <li>
                    <a href="/bookmark/"  title="Bookmark">Bookmark</a>
                  </li>
            
                  
                  <li>
                    <a href="http://shareup.sinaapp.com"  title="Share">Share</a>
                  </li>
            
                  
                  <li>
                    <a href="/about/"  title="About">About</a>
                  </li>
            
            <li>
                <a href="/atom.xml">
                    <span class="octicon octicon-rss" style="color:orange;"></span>
                </a>
            </li>
        </ul>
    </div>
</footer>


		<script src="/js/geopattern.js"></script>

		
			<script src="/js/toc.js"></script>
		

		<script src="/js/highlight.pack.js"></script>
		<script src="/lib/fancybox/jquery.fancybox-1.3.4.pack.js"></script>

		<script src="/js/index.js"></script>

		 <script src="/js/popular_repo.js"></script> 

	</body>
</html>